package com.sudoku.common;

import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONArray;
import com.sudoku.ui.Sudoku;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SqliteUtils {
	
	public static Connection getConnection() {
		Connection connection = null;
		try {
			Class.forName("org.sqlite.JDBC");
			/**
			 * 连接数据库
			 */
			connection = DriverManager.getConnection("jdbc:sqlite:" + Constants.SQLITE_DB_URL);
		} catch (Exception e) {
			System.out.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		
		return connection;
	}
	
	public static void createTable() {
		String sql =  "CREATE TABLE IF NOT EXISTS " + Constants.SQLITE_TABLE +"("
					+ "    id INT PRIMARY KEY,"
					+ "    time TEXT NOT NULL,"
					+ "    level INT NOT NULL,"
					+ "    tfCells TEXT NOT NULL,"
					+ "    mask TEXT NOT NULL,"
					+ "    puzzle TEXT NOT NULL)";
		try {
			Statement stmt = Constants.connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void closeConnection() {
		try {
			Constants.connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private static int getMaxId() {
		int maxId = -1;
		try {
			Statement stmt = Constants.connection.createStatement();
			String sql = "SELECT MAX(id) FROM " + Constants.SQLITE_TABLE;;
			ResultSet result = stmt.executeQuery(sql);
			maxId = result.getInt("MAX(id)");
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return maxId;
	}
	
	public static void insert(Sudoku sudoku) {
		
		/**
		 * 如果游戏已经结束，则不会将当前存档插入到数据库中
		 */
		if (sudoku.isGameOver()) {
			return;
		}
		
		int id = getMaxId()+1;
		String time = Utils.getNow();
		int level = sudoku.getLevel();
		String tfCells = Utils.tfCells2Json(sudoku.getPanel().getTfCells());
		String mask = Utils.mask2Json(sudoku.getPanel().getMask());
		String puzzle = Utils.puzzle2Json(sudoku.getPanel().getPuzzle());
		
		String sql =  "INSERT INTO " + Constants.SQLITE_TABLE + "(id, time, level, tfCells, mask, puzzle)"
					+ "VALUES(" + id + ", '" + time + "', " + level + ", '" + tfCells + "', '" + mask + "', '" + puzzle + "')";
		
		try {
			Statement stmt = Constants.connection.createStatement();
			stmt.executeUpdate(sql);
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void delete(int id) {
		try {
			Statement stmt = Constants.connection.createStatement();
			String sql = "DELETE FROM " + Constants.SQLITE_TABLE + " WHERE id=" + id;
			stmt.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
			return;
		}
	}

	public static List<Map<String, Object> > selectIdTimeAndLevel() {
		List<Map<String, Object> > list = new ArrayList<>();
		
		try {
			Statement stmt = Constants.connection.createStatement();
			String sql = "SELECT id, time, level FROM " + Constants.SQLITE_TABLE;
			ResultSet result = stmt.executeQuery(sql);
			
			while (result.next()) {
				Integer id = result.getInt("id");
				String time = result.getString("time");
				Integer level = result.getInt("level");
				
				Map<String, Object> map = new HashMap<String, Object>() {{
					put("id", id);
					put("time", time);
					put("level", level);
				}};
				
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public static String[][] selectTfCellsById(int id) {
		String[][] result = null;
		String sql = null;
		try {
			Statement stmt = Constants.connection.createStatement();
			sql = "SELECT tfCells FROM " + Constants.SQLITE_TABLE + " WHERE id=" + id;
			ResultSet rs = stmt.executeQuery(sql);
			result = JSONArray.parseObject(rs.getString("tfCells"), String[][].class);
		} catch (SQLException e) {
			System.out.println("SQL:" + sql);
			e.printStackTrace();
		}
		
		return result;
	}
	
	public static boolean[][] selectMaskById(int id) {
		boolean[][] result = null;
		
		try {
			Statement stmt = Constants.connection.createStatement();
			String sql = "SELECT mask FROM " + Constants.SQLITE_TABLE + " WHERE id=" + id;
			ResultSet rs = stmt.executeQuery(sql);
			result = JSONArray.parseObject(rs.getString("mask"), boolean[][].class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public static int[][] selectPuzzleById(int id) {
		int[][] result = null;
		
		try {
			Statement stmt = Constants.connection.createStatement();
			String sql = "SELECT puzzle FROM " + Constants.SQLITE_TABLE + " WHERE id=" + id;
			ResultSet rs = stmt.executeQuery(sql);
			result = JSONArray.parseObject(rs.getString("puzzle"), int[][].class);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return result;
	}
	
	public static int selectLevelById(int id) {
		int result = 0;
		
		try {
			Statement stmt = Constants.connection.createStatement();
			String sql = "SELECT level FROM " + Constants.SQLITE_TABLE + " WHERE id=" + id;
			ResultSet rs = stmt.executeQuery(sql);
			result = rs.getInt("level");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * 将数据库中的记录重新排序，删除不必要的记录，重新分配id
	 * 触发条件：数据库中的记录数>1000，或当前最大id>100000
	 */
	public static void maintainDatabase() {
		
	}
}
